Hierarchies in both SAP BW and Microsoft Multi-Dimensional OLAP cubes can have hidden properties. The properties are, in effect, flat attributes that show different data values for the given member elements in the visible hierarchy. Historically, these items were added to show more details for each item in analytic clients and reporting tools. Although there other techniques used today for this effect (including making the hidden items visible attributes in the cubes), many cube are still designed with these hidden attributes.
Pyramid allows users to access the hidden property attributes and use them in the tools through 2 related functions:
- Alternative Captions: use the properties to change the element captions, so they show different values in visualizations and slicer drop downs.
- Attribute Hierarchies: use the properties like normal flat attribute hierarchies in queries and visualizations, with some limitations.
The use of hidden properties as query attributes comes with some key limitations - dictated by the underlying MDX engines from SAP and MS. See below.
Accessing Hidden Hierarchies
After opening up a Microsoft OLAP cube, SAP BW cube or SAP Bex query, you will see an button (green circle below) in the dimensions tree panel. When it is off, it appears gray, and each hierarchy in the cube appears "normal" - red box below.
Clicking on the button will turn it blue, and a new set of property folders will appear under relevant hierarchies (purple box below). Each folder will contain the hidden properties (yellow arrow) or sub-folders, which may in turn contain other hidden properties.
Using the Hidden Properties in Queries
To use the hidden properties, simply drag them to the drop zones and use them like normal attribute hierarchies. You'll notice that the standard blue chip appears a little differently for the hidden properties: the color is a different shade of blue with a different icon (see below).
Limitations of the Hidden Properties in Queries
The hidden properties can be used like attribute chips in the drop zones, however there are some key limitations, driven by inherent limitations in both SAP and Microsoft's MDX engines:
- The hidden properties CANNOT be used in any query unless their parent attribute / hierarchy is used in the query as well.
- The hidden properties CANNOT be used if the parent attribute is used in the Filter drop zone (to create a slicer)
- The hidden properties CANNOT themselves be used in the Filter drop zone (to create a slicer)
- Various query-based operations are not available for hidden properties - like dicing, sorting, filtering, N-of-N. These are only available on the parent attribute.
With these limitations in mind, it is clear that hidden properties can be used to add more details to a query for visualization effect, but cannot be used analytically.
